package com.example.hjt_java_web_homework3.util;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author Li Chun Hei
 * @date 31/10/2025
 * @description
 */
@Component
public class DatabaseUtil {
    private static final Logger logger = LogManager.getLogger(DatabaseUtil.class);
    private static HikariDataSource dataSource;

    @PostConstruct
    public void init() {
        try {
            HikariConfig config = new HikariConfig();
            config.setJdbcUrl("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1");
            config.setUsername("sa");
            config.setPassword("");
            config.setDriverClassName("org.h2.Driver");
            config.setMaximumPoolSize(10);
            config.setMinimumIdle(2);
            config.setConnectionTimeout(30000);
            config.setIdleTimeout(600000);
            config.setMaxLifetime(1800000);

            dataSource = new HikariDataSource(config);
            initializeDatabase();
            logger.info("Database initialized successfully");
        } catch (Exception e) {
            logger.error("Failed to initialize database", e);
        }
    }

    private void initializeDatabase() throws SQLException {
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {

            // 创建设备表
            stmt.execute("DROP TABLE IF EXISTS devices");
            stmt.execute("CREATE TABLE devices (" +
                    "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                    "name VARCHAR(100) NOT NULL, " +
                    "type VARCHAR(50) NOT NULL, " +
                    "location VARCHAR(100), " +
                    "device_id VARCHAR(100) UNIQUE NOT NULL, " +
                    "status BOOLEAN DEFAULT TRUE, " +
                    "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
                    "update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");

            // 创建环境数据表
            stmt.execute("DROP TABLE IF EXISTS environment_data");
            stmt.execute("CREATE TABLE environment_data (" +
                    "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                    "temperature DECIMAL(5,2), " +
                    "humidity DECIMAL(5,2), " +
                    "energy_consumption DECIMAL(10,2), " +
                    "location VARCHAR(100), " +
                    "recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");

            // 创建用户表
            stmt.execute("DROP TABLE IF EXISTS users");
            stmt.execute("CREATE TABLE users (" +
                    "id BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                    "username VARCHAR(50) UNIQUE NOT NULL, " +
                    "password VARCHAR(100) NOT NULL, " +
                    "role VARCHAR(20) DEFAULT 'USER', " +
                    "enabled BOOLEAN DEFAULT TRUE, " +
                    "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");

            // 插入测试数据
            stmt.execute("INSERT INTO users (username, password, role) VALUES " +
                    "('admin', 'admin123', 'ADMIN'), " +
                    "('user', 'user123', 'USER')");

            stmt.execute("INSERT INTO devices (name, type, location, device_id) VALUES " +
                    "('客厅空调', 'AIR_CONDITIONER', '客厅', 'AC001'), " +
                    "('主卧电表', 'ELECTRIC_METER', '主卧', 'EM001')");

        }
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    @PreDestroy
    public static void closeDataSource() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
        }
    }
}
